'''
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

# 读取Excel文件
file_path = r'E:\桌面\无所属\a暑期实训\douban_comments1.xlsx'
df = pd.read_excel(file_path)

# 增加所需的列
df['recomment_id'] = df.index + 1
df['mark_id'] = df.index  + 1
df['user_id'] = 210001
df['re_assist'] = 0
df['re_top_not'] = 0

# 重命名列
df.rename(columns={'Comment': 'recomment'}, inplace=True)

# 选择所需的列
df = df[['recomment_id', 'mark_id', 'user_id',  'recomment', 're_assist', 're_top_not']]

# 数据库连接设置
DATABASE_URI = 'mysql+mysqlconnector://root:1234@localhost/film_remark'
engine = create_engine(DATABASE_URI)

# 将数据写入数据库
try:
    df.to_sql('recomment', con=engine, if_exists='append', index=False)
    print("数据写入成功")
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print("数据写入失败:", error)
'''


from sqlalchemy import create_engine, Table, Column, Integer, String, Boolean, MetaData
from sqlalchemy.sql import insert
from sqlalchemy.exc import SQLAlchemyError

# 创建数据库连接
engine = create_engine('mysql+mysqlconnector://root:1234@localhost/film_remark')

# 定义表结构
metadata = MetaData()
user_table = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_password', String(20), nullable=False),
    Column('user_name', String(20), nullable=False),
    Column('ban_not', Boolean, nullable=False),
    Column('ban_reason', String(200)),
    Column('ban_ma_id', Integer),
    Column('block_not', Boolean, nullable=False),
    Column('block_reason', String(200)),
    Column('block_ma_id', Integer),
    Column('complain_not', Boolean, nullable=False),
    Column('user_email', String(40), nullable=False),
    Column('complain_reason', String(200))
)

# 生成要插入的数据
data = []
for i in range(1, 101):
    data.append({
        'user_id': i,
        'user_password': '1234',
        'user_name': '僵尸',
        'ban_not': 1 if i <= 66 else 0,
        'ban_reason': '你被禁了' if i <= 66 else None,
        'ban_ma_id': 21002 if i <= 66 else None,
        'block_not': 1 if i > 66 else 0,
        'block_reason': '不好好发言，你没了' if i > 66 else None,
        'block_ma_id': 21002 if i > 66 else None,
        'complain_not': 1,
        'user_email': '11111111@qq.com',
        'complain_reason': '不要禁我不要禁我，我再也不敢了'
    })

# 插入数据
try:
    with engine.connect() as connection:
        for chunk in [data[i:i+20] for i in range(0, len(data), 20)]:
            stmt = insert(user_table).values(chunk)
            connection.execute(stmt)
        connection.commit()  # 提交事务
    print("数据插入完成。")
except SQLAlchemyError as e:
    print("数据插入失败：", e)


